mysql 聚集函数 count 使用详解

转载地址： http://www.cnblogs.com/oaks/p/5430958.html

本文将探讨以下问题

1.count(*) 、 count(n)、count(null)与count(fieldName)
2.distinct 与 count 连用
3.group by (多个字段) 与 count 实现分组计数
4.case when 语句与 count 连用实现按过滤计数

参考文章:
Select count(*)和Count（1）的区别和执行方式

 准备表以及数据

-- 创建表
CREATE TABLE `tb_student` (
  `id` int(11) NOT NULL,
  `stu_name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '学生姓名',
  `tea_name` varchar(255) DEFAULT NULL COMMENT '教师姓名',
  `stu_class` varchar(255) DEFAULT NULL COMMENT '所在班级名称',
  `stu_sex` varchar(255) DEFAULT NULL COMMENT '学生性别',
  `stu_sex_int` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入数据 
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('0', '小明', '老张', '一班', '男',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('1', '小红', '老张', '一班', '女',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('2', '小刚', '老王', '一班', '男',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('3', '小兰', '老王', '一班', '女',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('4', '小军', '老张', '二班', '男',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('5', '小芳', '老张', '二班', '女',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('6', '小强', '老王', '二班', '男',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('7', '小娜', '老王', '二班', '女',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('8', null, null, null, null,null);

问题一：count(*) 、 count(n)、count(null)与count(fieldName)

我们看一下执行过程:
EXPLAIN SELECT count(0) from tb_student
EXPLAIN SELECT count(*) from tb_student
两次执行结果相同，结果为: 
(select_type, table, type, possible_keys, key, key_len, ref, rows, Extra) VALUES ('1', 'SIMPLE', 'tb_student', 'index', NULL, 'stu_sex_int', '2', NULL, '8', 'Using index'); 
此次查询使用了索引stu_sex_int。我们知道某个字段建立索引之后数据库引擎会对该字段排序并把排序结果作为索引数据存储。查询时候对索引进行
二分查找提高命中率。在使用聚集函数同样也会使用索引。数据库引擎在处理count时,会直接从索引数据(排序结果中)中求排序结果的id最大值。这样一
来会大大提高count的速度。主键也有索引此处为什么没有使用主键的索引？因为stu_sex_int 字段长度更短 为tinyint 类型，查找速度更快。
由此可见 count(*)和count(n) n>=0 的效果相同。
当count的表达式为 NULL 时 不会计数 ,所以count(fieldName) 当fieldName 为null时 不会计数 。比如

• select count(stu_name) as count from tb_student; 结果: count=8
• select count(id) as count from tb_student; 结果: count=9
• select count(null) as count from tb_student ; 结果: count= 0

问题二: distinct 与 count 连用

distinct 的作用是对查询结果去重。distinct fieldA 那么在查询结果中 fieldA 的值不会重复。当 count 内的表达式是distinct时候
所表达的意思就是对被distinct的字段取值类型计。例如:

select distinct stu_class from tb_student;

执行结果:

stu_class
一班
二班

select count(distinct stu_class) as count from tb_student;
执行结果:

count
2

问题三：group by (多个字段) 与 count 实现分组计数

group by fieldA 是表示根据 fieldA 的不同取值对查询结果进行分组。比如对于 tb_student 表
根据 stu_sex 的不同取值 (男,女) 可把查询结果分成两组。fieldA 有n个不同的取值,查询结果就会被分成
n组。当分组字段有多个时候group by fieldA,fieldB 会对fieldA 和fieldB 进行排列组合。每个排列组合的
结果作为查询一个的一个分组。如果fileA 的取值有 n 个 fieldB的取值有 m 个，那么查询结果 将会被分称m*n
组。当count 与 group by 连用时，count是对 group by 结果的各个分组进行计数 。
单个分组条件:
SELECT stu_sex ,COUNT(*) as count from tb_student GROUP BY stu_sex ;
结果为:

stu_sex	count
NULL	1
男	4
女	4

多个分组条件:

    SELECT
        stu_sex,
        stu_class,
        COUNT(*) AS count
    FROM
        tb_student
    GROUP BY
        stu_sex,
        stu_class

结果为:

stu_sex	stu_class	count
NULL	NULL	1
女	一班	2
女	二班	2
男	一班	2
男	二班	2

问题四：case when 语句与 count 连用实现按过滤计数

在上述数据库中如果我们要查每个教师教了多少个 一班 同学 和二班同学 有两种方法
方法一:对教师和班级分组计数

      SELECT
        tea_name,
        stu_class,
        count(*) AS count
      FROM
        tb_student
      GROUP BY
        tea_name,
        stu_class

结果为:

tea_name	stu_class	count
NULL	NULL	1
老张	一班	2
老张	二班	2
老王	一班	2
老王	二班	2

这种方法不太直观我们可以把结果行转列更加清晰表达每个教师交每个班的人数

方法二:使用case when 行转列

      SELECT
        tea_name,
        count(case when stu_class='一班' then 1 else null end ) AS `一班人数` ,
        count(case when stu_class='二班' then 1 else null end ) AS `一班人数` 
      FROM
        tb_student
      GROUP BY
        tea_name

结果为:

tea_name	一班人数	一班人数
NULL	0	0
老张	2	2
老王	2	2

当关注的计数属性有较多取值时不适合这种用法，比如有100个班级，我们无法去写100个case when 。

在不加WHERE限制条件的情况下，COUNT(*)与COUNT(COL)基本可以认为是等价的；
但是在有WHERE限制条件的情况下，COUNT(*)会比COUNT(COL)快非常多；

COUNT(*)通常是对主键进行索引扫描，而COUNT(COL)就不一定了，另外前者是统计表中的所有符合的纪录总数，而后者是计算表中所有符合的COL的纪录数。还有有区别的。

COUNT的时候，如果没有WHERE限制的话，MySQL直接返回保存有总的行数，
而在有WHERE限制的情况下，总是需要对MySQL进行全表遍历。

count(1)与count(*)比较：

如果你的数据表没有主键，那么count(1)比count(*)快
如果有主键的话，那主键（联合主键）作为count的条件也比count(*)要快
如果你的表只有一个字段的话那count(*)就是最快的啦
count(*) count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(*),自动会优化指定到那一个字段。所以没必要去count(?)，用count(*),sql会帮你完成优化的

count详解：

count(*)将返回表格中所有存在的行的总数包括值为null的行，然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入）.
distinct 列名,得到的结果将是除去值为null和重复数据后的结果

1.任何情况下SELECT COUNT(*) FROM tablename是最优选择；
2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = 'value’ 这种查询；
3.杜绝SELECT COUNT(COL) FROM tablename的出现。